package com.llh.util;

import com.llh.controller.http.common.CommonExcuteTrade;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PoiUtil {
    static Logger logger = LogManager.getLogger(CommonExcuteTrade.class.getName());

    public static Map<String, Map<String, Object>> readSheet(File file, int sheetNum, int firstRow, int lastRow, String[] clomNames) throws Exception {
        //读取excel文件
        String absolutePath = file.getAbsolutePath();
        logger.info("读取的文件路径:" + absolutePath);
        if (absolutePath.endsWith(".xls")) {
            POIFSFileSystem fs = new POIFSFileSystem(file);
            //获取工作簿
            HSSFWorkbook workbook = HSSFWorkbookFactory.createWorkbook(fs);
            //获取获取表单
            HSSFSheet sheet = workbook.getSheetAt(sheetNum);
            //循环获取
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            if (firstRow < firstRowNum + 1 || lastRow > lastRowNum + 1) {
                throw new Exception("起始行号有误");
            }
            Map<String, Map<String, Object>> mapMap = new HashMap<>();
            for (int i = firstRow - 1; i < lastRow; i++) {
                Map<String, Object> map = new HashMap<>();
                HSSFRow row = sheet.getRow(i);
                short lastCellNum = row.getLastCellNum();
                int length = clomNames.length;
                if (lastCellNum != length) {
                    throw new Exception("提示每行的数据数和数据名列表不一致:" + lastCellNum + "-" + length);
                }
                for (int j = 0; j < lastCellNum; j++) {
                    HSSFCell cell = row.getCell(j);
                    Object cellValue = getCellValue(cell);
                    map.put(clomNames[j], cellValue);
                }
                mapMap.put(String.valueOf(i), map);
            }
            return mapMap;
        } else if (absolutePath.endsWith(".xlsx")) {
            /*POIFSFileSystem fs = new POIFSFileSystem(file);
            //获取工作簿
            XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook();*/
            FileInputStream fileInputStream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //获取获取表单
            XSSFSheet sheet = workbook.getSheetAt(sheetNum);
            //循环获取
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            if (firstRow < firstRowNum + 1 || lastRow > lastRowNum + 1) {
                throw new Exception("起始行号有误");
            }
            Map<String, Map<String, Object>> mapMap = new HashMap<>();
            for (int i = firstRow - 1; i < lastRow; i++) {
                Map<String, Object> map = new HashMap<>();
                XSSFRow row = sheet.getRow(i);
                short lastCellNum = row.getLastCellNum();
                int length = clomNames.length;
                if (lastCellNum != length) {
                    throw new Exception("提示每行的数据数和数据名列表不一致:" + lastCellNum + "-" + length);
                }
                for (int j = 0; j < lastCellNum; j++) {
                    XSSFCell cell = row.getCell(j);
                    Object cellValue = getCellValue(cell);
                    map.put(clomNames[j], cellValue);
                }
                mapMap.put(String.valueOf(i), map);
            }
            return mapMap;
        } else {
            throw new Exception("文件路径后缀不合法");
        }

    }

    public static Object getCellValue(Cell cell) {

        if (cell == null) {
            return null;

        }

        if (CellType.STRING == cell.getCellType()) {
            return cell.getStringCellValue() == null || "".equals(cell.getStringCellValue()) || "——".equals(cell.getStringCellValue()) ? null : cell.getStringCellValue().trim();
        } else if (CellType.NUMERIC == cell.getCellType()) {
            if (DateUtil.isCellDateFormatted(cell)) {
                double d = cell.getNumericCellValue();
                return new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(d));
            } else {
                DecimalFormat df = new DecimalFormat("#.####");
                return df.format(cell.getNumericCellValue()).trim();
            }
        } else if (CellType.BOOLEAN == cell.getCellType()) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (CellType.FORMULA == cell.getCellType()) {
            return cell.getCellFormula();
        } else if (CellType.BLANK == cell.getCellType() || CellType.ERROR == cell.getCellType()) {
            return null;
        }

        return null;
    }

    public static Map<String, Map<String, Object>> readSheet02(File file, int sheetNum, int firstRow, int lastRow, String[] clomNames) throws Exception {
        //读取excel文件
        String absolutePath = file.getAbsolutePath();
        logger.info("读取的文件路径:" + absolutePath);
        if (absolutePath.endsWith(".xls")) {
            POIFSFileSystem fs = new POIFSFileSystem(file);
            //获取工作簿
            HSSFWorkbook workbook = HSSFWorkbookFactory.createWorkbook(fs);
            //获取获取表单
            HSSFSheet sheet = workbook.getSheetAt(sheetNum);
            //循环获取
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            if (firstRow < firstRowNum + 1 || lastRow > lastRowNum + 1) {
                throw new Exception("起始行号有误");
            }
            Map<String, Map<String, Object>> mapMap = new HashMap<>();
            for (int i = firstRow - 1; i < lastRow; i++) {
                Map<String, Object> map = new HashMap<>();
                HSSFRow row = sheet.getRow(i);
                short lastCellNum = row.getLastCellNum();
                int length = clomNames.length;
                if (lastCellNum != length) {
                    logger.error("这一行的数据不完整");
//                    throw new Exception("提示每行的数据数和数据名列表不一致:" + lastCellNum + "-" + length);
                }
                for (int j = 0; j < clomNames.length; j++) {
                    HSSFCell cell = row.getCell(j);
                    Object cellValue = getCellValue(cell);
                    map.put(clomNames[j], cellValue);
                }
                mapMap.put(String.valueOf(i), map);
            }
            return mapMap;
        } else if (absolutePath.endsWith(".xlsx")) {
            /*POIFSFileSystem fs = new POIFSFileSystem(file);
            //获取工作簿
            XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook();*/
            FileInputStream fileInputStream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //获取获取表单
            XSSFSheet sheet = workbook.getSheetAt(sheetNum);
            //循环获取
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            if (firstRow < firstRowNum + 1 || lastRow > lastRowNum + 1) {
                throw new Exception("起始行号有误");
            }
            Map<String, Map<String, Object>> mapMap = new HashMap<>();
            for (int i = firstRow - 1; i < lastRow; i++) {
                Map<String, Object> map = new HashMap<>();
                XSSFRow row = sheet.getRow(i);
                short lastCellNum = row.getLastCellNum();
                int length = clomNames.length;
                if (lastCellNum != length) {
                    logger.error("这一行的数据不完整");
//                    throw new Exception("提示每行的数据数和数据名列表不一致:" + lastCellNum + "-" + length);
                }
                for (int j = 0; j < clomNames.length; j++) {
                    XSSFCell cell = row.getCell(j);
                    Object cellValue = getCellValue(cell);
                    map.put(clomNames[j], cellValue);
                }
                mapMap.put(String.valueOf(i), map);
            }
            return mapMap;
        } else {
            throw new Exception("文件路径后缀不合法");
        }

    }
}
